import hashlib
import itertools
import logging
import json
import time
from functools import reduce
from operator import or_

from flask import current_app as app, url_for, request_started
from flask_login import current_user, AnonymousUserMixin, UserMixin
from passlib.apps import custom_app_context as pwd_context
from sqlalchemy.exc import DBAPIError
from sqlalchemy.dialects import postgresql

from sqlalchemy_utils import EmailType
from sqlalchemy_utils.models import generic_repr

from redash import redis_connection
from redash.utils import generate_token, utcnow, gen_query_hash

from .base import db, Column, GFKBase, key_type, primary_key
from .mixins import TimestampMixin, BelongsToOrgMixin
from .types import json_cast_property, MutableDict, MutableList

logger = logging.getLogger(__name__)


@generic_repr("id", "code", "name","org_id","items")
class Enums(db.Model):
    id = Column(db.Integer, primary_key=True)
    org_id = Column(key_type("Organization"), db.ForeignKey("organizations.id"))
    org = db.relationship("Organization", back_populates="enums")
    code = Column(db.String(100))
    name = Column(db.String(255))
    is_builtin = Column(db.Boolean(False))
    items = Column(
        MutableList.as_mutable(postgresql.ARRAY(postgresql.JSON)),
        nullable=True,
        server_default=json.dumps({}),
        default=[],
    )

    __tablename__ = "enums"
    __table_args__ = (db.Index("enums_org_code","org_id", "code", unique=True),)

    def __str__(self):
        return "%s,%s" % (
            self.code,
            self.name,
        )

    def to_dict(self):
        return {
            "id": self.id,
            "code": self.code,
            "name": self.name,
            "is_builtin": self.is_builtin,
        }

    @classmethod
    def get_by_code(cls, code,org):
        return cls.query.filter(cls.org == org,cls.code == code).one()

    @classmethod
    def all(cls,org):
        return cls.query.filter(cls.org == org).order_by(cls.id.asc())
    
    @classmethod
    def get_code(cls,_tblname,_tblfield,_bill_date,_org_id,_type):
        strsql="""select array_to_json(array_agg(row_to_json(t))) from (select public.gaincode(:tblname,:tblfield,:bill_date,:org_id,:type) as code) t """
        return db.session.execute(strsql,{"tblname": _tblname,"tblfield": _tblfield,"bill_date": _bill_date,"org_id": _org_id,"type": _type}).fetchone()

    @classmethod
    def get_pagequery(cls,_tblname,_strgetfield,_orderfield,_pagesize,_pageindex,_ordertype,_strwhere):
        strsql="""select public.overpage(:tblname,:strgetfield,:orderfield,:pagesize,:pageindex,:ordertype,:strwhere)"""
        return db.session.execute(strsql,{"tblname": _tblname,"strgetfield": _strgetfield,"orderfield": _orderfield,"pagesize": _pagesize
                                          ,"pageindex": _pageindex,"ordertype": _ordertype,"strwhere": _strwhere}).fetchone()
    @classmethod
    def get_selsql(cls,strsql):
        return db.session.execute(strsql).fetchone()

@generic_repr("id", "code", "name")
class OrgUnit(TimestampMixin, BelongsToOrgMixin, db.Model):
    id = Column(db.Integer, primary_key=True)
    org_id = Column(db.Integer, db.ForeignKey("organizations.id"))
    org = db.relationship("Organization", backref=db.backref("orgunit", lazy="dynamic"))
    code = Column(db.String(100))
    name = Column(db.String(255))
    shortname = Column(db.String(100), nullable=True)
    is_active = Column(db.Boolean(True), nullable=True)
    orgunit_enums = Column(
        MutableList.as_mutable(postgresql.ARRAY(db.String(100))), nullable=True
    )

    __tablename__ = "orgunit"
    __table_args__ = (db.Index("orgunit_org_code", "org_id", "code", unique=True),)

    def __str__(self):
        return "%s,%s" % (
            self.code,
            self.name,
        )

    def to_dict(self):
        return {
            "id": self.id,
            "code": self.code,
            "name": self.name,
            "shortname": self.shortname,
            "is_active": self.is_active,
            "orgunit_enums": self.orgunit_enums,
        }

    @classmethod
    def get_by_code(cls, org, code):
        return cls.get_by_org(org).filter(cls.code == code).one()

    @classmethod
    def all(cls, org):
        return cls.get_by_org(org)

    @classmethod
    def by_orgunit_enum(cls, org, orgunit_enum):
        return cls.get_by_org(org).filter(cls.orgunit_enums.any(orgunit_enum))

    @classmethod
    def get_by_org(cls, org):
        return cls.query.filter(cls.org == org)

    @classmethod
    def get_by_id(cls, id):
        return cls.query.filter(cls.id == id).one()

    @classmethod
    def search(cls, base_query, term):
        term = "%{}%".format(term)
        search_filter = cls.name.ilike(term)

        return base_query.filter(search_filter)

@generic_repr("id", "strcustomercode", "strcustomername")
class Customers(db.Model):
    id = Column(db.Integer, primary_key=True)
    org_id = Column(key_type("Organization"), db.ForeignKey("organizations.id"))
    org = db.relationship("Organization", back_populates="customers")
    lngcustomerid =  Column(db.Integer, default=0)
    strcustomercode = Column(db.String(255))
    strcustomername = Column(db.String(255))
    lngcustomertypeid =  Column(db.Integer, default=0, nullable=True)
    strcustomertypecode =  Column(db.String(255), default=" ", nullable=True)
    strcustomertypename = Column(db.String(255), default=" ", nullable=True)
    blnisinactive =  Column(db.Boolean(True), default=True, nullable=True)
    bytstatus =  Column(db.Integer, default=0, nullable=True)
    blnchuli =  Column(db.Integer, default=0, nullable=True)
    representative =  Column(db.String(50), nullable=True)
    mobile =  Column(db.String(50), nullable=True)
    address =  Column(db.String(500), nullable=True)
    userid = Column(db.Integer) #录入人ID

    __tablename__ = "customers"
    __table_args__ = (db.Index("customers_org_code","org_id", "strcustomercode", unique=True),)

    #__table_args__ = {'extend_existing': True}
    def __str__(self):
        return "%s,%s" % (
            self.strcustomercode,
            self.strcustomername,
        )

    def to_dict(self):
        return {
            "id": self.id,
            "lngcustomerid": self.lngcustomerid,
            "strcustomercode": self.strcustomercode,
            "strcustomername": self.strcustomername,
            "lngcustomertypeid": self.lngcustomertypeid,
            "strcustomertypecode": self.strcustomertypecode,
            "strcustomertypename": self.strcustomertypename,
            "representative": self.representative,
            "mobile": self.mobile,
            "address": self.address,
            "blnisinactive": self.blnisinactive,
            "bytstatus": self.bytstatus,
            "blnchuli": self.blnchuli,
            "userid": self.userid,
        }
    @classmethod
    def get_by_id(cls, id):
        return cls.query.filter(cls.id == id).one()

    @classmethod
    def get_id(cls):
        strsql="""select array_to_json(array_agg(row_to_json(t))) from (select nextval('customers_id_seq'::regclass) as id) t """
        return db.session.execute(strsql).fetchone()

    @classmethod
    def get_by_code(cls, code,org):
        return cls.query.filter(cls.org == org,cls.strcustomercode == code).one()

    @classmethod
    def get_by_status(cls, bytstatus,org):
        return cls.query.filter(cls.org == org,cls.bytstatus == bytstatus,cls.blnisinactive == True)




################################################
################设置20200914###############################################
@generic_repr("id", "code", "type")
class GlobalSetting(TimestampMixin, BelongsToOrgMixin, db.Model):
    id = Column(db.Integer, primary_key=True)
    org_id = Column(key_type("Organization"), db.ForeignKey("organizations.id"))
    org = db.relationship("Organization", back_populates="globalsetting")
    userid = Column(db.Integer) #--操作员ID
    type = Column(db.String(100)) #--模块
    code = Column(db.String(100)) #KEY
    value = Column(db.String(100)) #值
    message = Column(db.Text, default=None, nullable=True)  #  说明

    __tablename__ = "globalsetting"
    __table_args__ = (db.Index("globalsetting_org_type_code","org_id","code", "type", unique=True),)

    def __str__(self):
        return "%s,%s" % (
            self.code,
            self.type,
        )

    def to_dict(self):
        return {
            "id": self.id,
            "userid": self.userid,
            "type": self.type,
            "code": self.code,
            "value": self.value,
            "message": self.message,
            "updated_at": self.updated_at,
            "created_at": self.created_at,
        }

    @classmethod
    def get_by_id(cls, id):
        return cls.query.filter(cls.id == id).one()
    
@generic_repr("id", "orgstruct_enum")
class OrgStruct(TimestampMixin, BelongsToOrgMixin, db.Model):
    id = Column(db.Integer, primary_key=True)
    org_id = Column(db.Integer, db.ForeignKey("organizations.id"))
    org = db.relationship("Organization", backref=db.backref("orgstruct", lazy="dynamic"))
    orgstruct_enum = Column(db.String(100))
    effect_date = Column(db.Date)
    expire_date = Column(db.Date, default=None, nullable=True)
    details = Column(
        MutableList.as_mutable(postgresql.ARRAY(postgresql.JSON)),
        nullable=True,
        server_default=json.dumps({}),
        default=[],
    )

    __tablename__ = "orgstruct"
    __table_args__ = (db.Index("orgstruct_org_orgstruct_effect","org_id", "orgstruct_enum", "effect_date", unique=True),)

    def __str__(self):
        return "%s,%s" % (
            self.orgstruct_enum,
            self.effect_date,
        )

    def to_dict(self):
        return {
            "id": self.id,
            "effect_date": self.effect_date,
            "expire_date": self.expire_date,
        }

    def to_dict_full(self):
        return {
            "id": self.id,
            "effect_date": self.effect_date,
            "expire_date": self.expire_date,
            "orgstruct_enum": self.orgstruct_enum,
            "details": self.details,
        }

    @classmethod
    def get_by_effect_date(cls, org, orgstruct_enum, effect_date):
        return cls.all(org, orgstruct_enum).filter(cls.effect_date == effect_date).one_or_none()

    @classmethod
    def get_last_by_org_orgstruct_enum(cls, org, orgstruct_enum):
        os = cls.all(org, orgstruct_enum)
        return os.first() if os else None

    @classmethod
    def get_last(cls, os):
        os = cls.all(os.org, os.orgstruct_enum).filter(cls.id < os.id).order_by(cls.id.desc())
        return os.first() if os else None

    @classmethod
    def get_next(cls, os):
        os = cls.all(os.org, os.orgstruct_enum).filter(cls.id > os.id).order_by(cls.id.asc())
        return os.first() if os else None

    @classmethod
    def all(cls, org, orgstruct_enum):
        return cls.get_by_org(org).filter(cls.orgstruct_enum == orgstruct_enum).order_by(cls.effect_date.desc())

    @classmethod
    def get_by_org(cls, org):
        return cls.query.filter(cls.org == org)

    @classmethod
    def get_by_id(cls, _id):
        return cls.query.filter(cls.id == _id).one()

    def is_used(self):
        return False

    def has_orgunit(self, orgunit_id):
        if self.details is None or len(self.details) == 0:
            return False
        else:
            return True if str(self.details).find(": \'" + str(orgunit_id) + "\',") >= 0 else False
        
#==========================================列表显示隐藏列=======
@generic_repr("id", "code", "name")
class QueryShowField(TimestampMixin, BelongsToOrgMixin, db.Model):
    id = Column(db.Integer, primary_key=True)
    org_id = Column(key_type("Organization"), db.ForeignKey("organizations.id"))
    org = db.relationship("Organization", back_populates="queryshowfield")
    code = Column(db.String(100))
    name = Column(db.String(255))
    fieldsshow = Column(
        MutableList.as_mutable(postgresql.ARRAY(db.String(255))), nullable=True
    )
    fieldsall = Column(
        MutableList.as_mutable(postgresql.JSON),
        nullable=True,
        server_default="{}",
        default={},
    ) #附件
    message = Column(db.Text, default=None, nullable=True)  #  纪录事项
    userid = Column(db.Integer) #录入人ID

    __tablename__ = "queryshowfield"
    __table_args__ = (db.Index("queryshowfield_org_code","org_id", "code", unique=True),)

    def __str__(self):
        return "%s,%s" % (
            self.code,
            self.name,
        )

    def to_dict(self):
        return {
            "id": self.id,
            "code": self.code,
            "name": self.name,
            "fieldsshow": self.fieldsshow,
            "fieldsall": self.fieldsall,
            "message": self.message,
            "userid": self.userid,
            "updated_at": self.updated_at,
            "created_at": self.created_at,
        }

    @classmethod
    def get_by_code(cls, code):
        return cls.query.filter(cls.code == code).one()

    @classmethod
    def get_by_id(cls, id):
        return cls.query.filter(cls.id == id).one()
    